IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_MediaDelete]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_MediaDelete]
GO

CREATE PROCEDURE [dbo].[cms_sp_MediaDelete]
	@Id INT
AS
	WITH Direct(MediaId ,ParentMediaId) AS 
	     (
	         SELECT m.MediaId
	               ,m.ParentMediaId
	         FROM   Media m
	         WHERE  m.MediaId = @Id
	         UNION ALL
	         SELECT m.MediaId
	               ,m.ParentMediaId
	         FROM   Media m
	                INNER JOIN Direct AS d
	                     ON  d.MediaId = m.ParentMediaId
	     )
	
	DELETE 
	FROM   Media
	       FROM Media m
	       JOIN Direct d
	            ON  d.MediaId = m.MediaId
GO